package com.windfone.dao.impl.jdbc.mysql;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.common.SQLUtil;
import com.windfone.common.StringUtil;
import com.windfone.dao.RecommendDao;
import com.windfone.model.Recommend;
import com.windfone.model.RecommendType;

public class RecommendDaoImpl implements RecommendDao {
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	String SQL_DELETE_RECOMMEND=" DELETE FROM  recommend WHERE id=?";
	public int  deleRecommend(final Recommend bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_DELETE_RECOMMEND,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String SQL_GET_RECOMMEND_BYID=" SELECT r.*,t.name as recommendTypeName FROM  recommend r,recommendType t WHERE id=? AND r.recommendTypeId=t.id";
	public List<Recommend> getRecommendById(final int keyValue) {
		final List<Recommend> result = new ArrayList<Recommend>();
		try {
			jdbcTemplate.query(
					SQL_GET_RECOMMEND_BYID,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, keyValue);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Recommend to= new Recommend();
//							to.setName(rs.getString("name"));
							to.setRecordId(rs.getInt("recordId"));
							to.setPositionSeq(rs.getInt("positionSeq"));
							to.setRecommendTypeId(rs.getInt("recommendTypeId"));
							to.setRecommendTypeName(rs.getString("recommendTypeName"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	private String GET_ALL_RECOMMEND_TYPE = " SELECT * FROM recommendtype 	";
	public List<RecommendType> listAllRecommendType() {
		final List<RecommendType> result = new ArrayList<RecommendType>();
		try {
			jdbcTemplate.query(
					GET_ALL_RECOMMEND_TYPE,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							RecommendType to= new RecommendType();
							to.setId(rs.getInt("id"));
							to.setName(rs.getString("name"));
							to.setTableName(rs.getString("tableName"));
							to.setBeanName(rs.getString("beanName"));
							to.setFields(rs.getString("fields"));
							to.setFieldsLabel(rs.getString("fieldsLabel"));
							to.setTarget(rs.getString("target"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

//	String SQL_UPDATE_RECOMMEND=" UPDATE product SET recordId=?,recommendTypeId=?,positionSeq=?   WHERE id=?";
	String SQL_UPDATE_RECOMMEND=" UPDATE recommend SET positionSeq=?   WHERE id=?";
	public int updateRecommendById(final Recommend bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_UPDATE_RECOMMEND,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
//						ps.setInt(i++, bean.getRecordId());
//						ps.setInt(i++, bean.getRecommendTypeId());
						ps.setInt(i++, bean.getPositionSeq());
						ps.setInt(i++, bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String SQL_INSERT_RECOMMEND=" INSERT INTO recommend ( recordId,recommendTypeId,positionSeq ) VALUES(?,?,? )";
	public int addRecommend(final Recommend bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_INSERT_RECOMMEND,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++, bean.getRecordId());
						ps.setInt(i++, bean.getRecommendTypeId());
						ps.setInt(i++, bean.getPositionSeq());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
//	String SQL_GET_RECOMMEND_BY_RECOMMEND_ID=" SELECT r.*,t.name as recommendTypeName FROM  recommend r,recommendType t WHERE r.recommendTypeId=? AND r.recommendTypeId=t.id";
	public List<Recommend> getRecommendByRecommendTypeId(final int recommendTypeId) {
		final List result = new ArrayList ();
		final RecommendType typeBean = getRecommendTypeById(recommendTypeId).get(0);
		String sql = " SELECT m.*,r.positionSeq,r.id recommendId  FROM "+typeBean.getTableName() + 
		" m ,recommend r,recommendtype t  WHERE r.recordId=m.id AND r.recommendTypeId=t.id AND t.id="+recommendTypeId;
		
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							String beanName = typeBean.getBeanName();
							String fields = typeBean.getFields();
							String path = "com.windfone.model."+beanName;
							try {
								Class c = Class.forName(path);
								Constructor ct = c.getConstructor(null);
								Object obj = ct.newInstance(null);
								String[] fds = fields.split(",");
								for(int i=0;i<fds.length;i++){
									Field field= c.getDeclaredField(fds[i]);
									Object value = SQLUtil.getValue(rs,fds[i],field.getType());
									 String setMethodName = "set"+StringUtil.toFirstLetterUpperCase( fds[i] ); 
									 Method setMethod = c.getMethod(setMethodName, field.getType());
									 setMethod.invoke(obj, value);
								}
								fields = "positionSeq,recommendId";
								fds = fields.split(",");
								for(int i=0;i<fds.length;i++){
									Field field= c.getSuperclass().getDeclaredField(fds[i]);
									Object value = SQLUtil.getValue(rs,fds[i],field.getType());
									String setMethodName = "set"+StringUtil.toFirstLetterUpperCase( fds[i] ); 
									Method setMethod = c.getMethod(setMethodName, field.getType());
									setMethod.invoke(obj, value);
								}
								
					            result.add(obj);
							} catch ( Exception e) {
								e.printStackTrace();
							}
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
/*	String SQL_GET_RECOMMEND_BY_RECOMMEND_ID=" SELECT r.*,t.name as recommendTypeName FROM  recommend r,recommendType t WHERE r.recommendTypeId=? AND r.recommendTypeId=t.id";
	public List<Recommend> getRecommendByRecommendTypeId(final int recommendTypeId) {
		final List<Recommend> result = new ArrayList<Recommend>();
		try {
			jdbcTemplate.query(
					SQL_GET_RECOMMEND_BY_RECOMMEND_ID,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, recommendTypeId);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Recommend to= new Recommend();
//							to.setName(rs.getString("name"));
							to.setRecordId(rs.getInt("recordId"));
							to.setPositionSeq(rs.getInt("positionSeq"));
							to.setRecommendTypeId(rs.getInt("recommendTypeId"));
							to.setRecommendTypeName(rs.getString("recommendTypeName"));
							to.setId(rs.getInt("id"));
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
*/	
	private String GET_RECORDS_BY_TABLE_NAME = " SELECT * FROM ";
	public List<Object> getRecordsByTableName(final String tableName, final String beginDate,final	String endDate, final String beanName,final String fields) {
		final List<Object> result = new ArrayList<Object> ();
		String condition = " WHERE 1=1 ";
		if ( !beginDate.equals(""))
			condition = condition + " AND createTime>='" +beginDate +"'";
		if ( !endDate.equals(""))
			condition = condition + " AND createTime<='"+endDate+"'";
		String sql = GET_RECORDS_BY_TABLE_NAME + tableName   + condition+ " LIMIT 200";
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							String path = "com.windfone.model."+beanName;
							try {
								Class c = Class.forName(path);
								Constructor ct = c.getConstructor(null);
								Object obj = ct.newInstance(null);
								String[] fds = fields.split(",");
								for(int i=0;i<fds.length;i++){
									Field field= c.getDeclaredField(fds[i]);
									Object value = SQLUtil.getValue(rs,fds[i],field.getType());
									 String setMethodName = "set"+StringUtil.toFirstLetterUpperCase( fds[i] ); 
									 Method setMethod = c.getMethod(setMethodName, field.getType());
									 setMethod.invoke(obj, value);
								}
					            result.add(obj);
							} catch ( Exception e) {
								e.printStackTrace();
							}
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private String GET_RECOMMEND_TYPE_BYID = " SELECT * FROM recommendtype WHERE id=?	";
	public List<RecommendType> getRecommendTypeById(final int recommendTypeId) {
		final List<RecommendType> result = new ArrayList<RecommendType>();
		try {
			jdbcTemplate.query(
					GET_RECOMMEND_TYPE_BYID,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, recommendTypeId);
						}
					},					
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							RecommendType to= new RecommendType();
							to.setId(rs.getInt("id"));
							to.setName(rs.getString("name"));
							to.setTableName(rs.getString("tableName"));
							to.setBeanName(rs.getString("beanName"));
							to.setFields(rs.getString("fields"));
							to.setFieldsLabel(rs.getString("fieldsLabel"));
							to.setTarget(rs.getString("target"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

 

}
